const MYSQL = require("../../sql/mysql");
const provincesSqlName = "address_provinces";
const citiesSqlName = "address_cities";
const districtsSqlName = "address_districts";
const countrySqlName = "address_country";
/**
 * 国家列表
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function countryList(Request) {
  try {
    let query = Request.query;
    let { page = 1, limit = 10 } = query;
    limit = limit < 1 ? (limit = 1) : limit;
    page = page < 1 ? (page = 1) : page;
    const conn = await MYSQL.promise().getConnection();
    let StartQuery = await conn.query(`
    SELECT *  
    ,(
      SELECT COUNT(*) FROM ${provincesSqlName}
      WHERE ${provincesSqlName}.parent = ${countrySqlName}.code 
    ) AS childrenNumber
    FROM
    ${countrySqlName}  
    LIMIT ${(page - 1) * limit + "," + page * limit}
    `);
    // 获取数据总数 =====
    let totalResult = await conn.query(
      `SELECT Count(*) AS total FROM ${countrySqlName}`
    );
    let total = totalResult[0][0].total;
    // 释放Mysql池
    await conn.release();
    let pages = Math.ceil(total / limit);
    return {
      code: 0,
      msg: "获取成功",
      data: {
        pages,
        page,
        limit,
        list: StartQuery[0],
        total,
      },
    };
  } catch (error) {
    console.log(error);
    throw { code: 500, msg: "操作失败,查询国家错误" };
  }
}

/***
 *
 * 国家列表（不分页）
 *
 */
async function countryListAll() {
  try {
    // 开始MYSQL数据库对比查询 ================================
    const conn = await MYSQL.promise().getConnection();
    // 开始查询MYSQL数据
    let StartMysqlQuery = await conn.query(`
    SELECT *  
    ,(
      SELECT COUNT(*) FROM ${provincesSqlName}
      WHERE ${provincesSqlName}.parent = ${countrySqlName}.code 
    ) AS childrenNumber 
    FROM
    ${countrySqlName}  
    `);
    // 释放Mysql池
    await conn.release();
    // 取出数据
    let countryList = StartMysqlQuery[0];
    return { code: 0, msg: "获取成功", data: countryList };
  } catch (error) {
    throw { code: 500, msg: "获取国家列表出现错误" };
  }
}

/**
 * 删除国家
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function deleteCountry(params = {}) {
  try {
    let { id } = params;
    let query = `
      REMOVE ${countrySqlName} WHERE id = ${id}
    `;
    let result = await MYSQL.queryPromise(query);
    return {
      code: 0,
      msg: "删除成功",
    };
  } catch (error) {
    throw { code: 500, msg: "删除省份失败" };
  }
}
/**
 * 新增国家
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function addCountry(params = {}) {}
/**
 * 省份列表
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function provincesList(Request) {
  try {
    let query = Request.query;
    let { page = 1, limit = 10, parent } = query;
    if (!parent) {
      throw {
        code: 500,
        msg: "缺少parent字段",
      };
    }
    limit = limit < 1 ? (limit = 1) : limit;
    page = page < 1 ? (page = 1) : page;
    const conn = await MYSQL.promise().getConnection();
    let startQuery = await conn.query(`
    SELECT *,(
      SELECT COUNT(*) FROM ${citiesSqlName}
      WHERE ${citiesSqlName}.parent = ${provincesSqlName}.code 
    ) AS childrenNumber  
    FROM ${provincesSqlName}
    WHERE parent = ${parent}
    LIMIT ${(page - 1) * limit + "," + page * limit}
    `);
    // 获取数据总数 =====
    let totalResult = await conn.query(
      `
         SELECT Count(*) AS total FROM ${provincesSqlName} WHERE parent = ${parent}
      `
    );
    let total = totalResult[0][0].total;
    let list = startQuery[0];
    let pages = Math.ceil(total / limit);
    // 释放Mysql池
    await conn.release();
    return {
      code: 0,
      msg: "获取成功",
      data: {
        pages,
        page,
        limit,
        list,
      },
    };
  } catch (error) {
    let msg = error.msg || error.message;
    throw { code: 500, msg };
  }
}

/**
 * 省份列表（不分页）
 */
async function provincesListAll() {
  try {
    // 开始MYSQL数据库对比查询 ================================
    const conn = await MYSQL.promise().getConnection();
    // 开始查询MYSQL数据
    let StartMysqlQuery = await conn.query(`
    SELECT *,(
      SELECT COUNT(*) FROM ${citiesSqlName}
      WHERE ${citiesSqlName}.parent = ${provincesSqlName}.code 
    ) AS childrenNumber  
    FROM ${provincesSqlName}
    `);
    // 释放Mysql池
    await conn.release();
    // 取出数据
    let countryList = StartMysqlQuery[0];
    return { code: 0, msg: "获取成功", data: countryList };
  } catch (error) {
    throw { code: 500, msg: "获取省份列表出现错误" };
  }
}
/**
 * 城市列表
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function citiesList(params = {}) {
  let { provinces, page, limit } = params;
  try {
    let query = `
    SELECT * ,(
      SELECT COUNT(*) FROM ${districtsSqlName}
      WHERE ${districtsSqlName}.parent = ${citiesSqlName}.code 
    ) AS childrenNumber
    FROM ${citiesSqlName} 
    WHERE parent = '${provinces}'
    LIMIT ${(page - 1) * limit + "," + page * limit}
    `;

    let list = await MYSQL.queryPromise(query);

    // 获取数据总数 =====
    let totalResult = await MYSQL.promise().query(
      `
       SELECT Count(*) AS total FROM ${provincesSqlName} WHERE parent = '${provinces}'
      `
    );
    let total = totalResult[0][0].total;
    let pages = Math.ceil(total / limit);
    return {
      code: 0,
      msg: "获取成功",
      data: {
        page,
        pages,
        provinces,
        list,
      },
    };
  } catch (error) {
    throw { code: 500, msg: "操作失败,查询城市错误" };
  }
}

/**
 * 城市列表(不分页)
 *
 */
async function citiesListAll() {
  try {
    // 开始MYSQL数据库对比查询 ================================
    const conn = await MYSQL.promise().getConnection();
    // 开始查询MYSQL数据
    let StartMysqlQuery = await conn.query(`
        SELECT * ,(
          SELECT COUNT(*) FROM ${districtsSqlName}
          WHERE ${districtsSqlName}.parent = ${citiesSqlName}.code 
        ) AS childrenNumber
        FROM ${citiesSqlName} 
        `);
    // 释放Mysql池
    await conn.release();
    // 取出数据
    let countryList = StartMysqlQuery[0];
    return { code: 0, msg: "获取成功", data: countryList };
  } catch (error) {
    throw { code: 500, msg: "操作失败,查询城市错误" };
  }
}

/**
 * 区县列表
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function districtsList(params = {}) {
  let { cities, page, limit } = params;
  try {
    let query = `SELECT * FROM ${districtsSqlName} WHERE parent = '${cities}'`;
    let list = await MYSQL.queryPromise(query);
    return {
      code: 0,
      msg: "获取成功",
      data: {
        page,
        pages: null,
        cities,
        list,
      },
    };
  } catch (error) {
    throw { code: 500, msg: "操作失败,查询区县错误" };
  }
}
/**
 * 删除省份
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function removeProvinces(parmas = {}) {
  try {
    let { id } = parmas;

    let query = `
            REMOVE ${provincesSqlName} WHERE id = ${id}
        `;
    let result = await MYSQL.queryPromise(query);
    return {
      code: 0,
      msg: "删除成功",
    };
  } catch (error) {
    throw { code: 500, msg: "删除省份失败" };
  }
}
/**
 * 删除城市
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function removeCities(parmas = {}) {
  try {
    let { id } = parmas;

    let query = `
            REMOVE ${citiesSqlName} WHERE id = ${id}
        `;
    let result = await MYSQL.queryPromise(query);
    return {
      code: 0,
      msg: "删除成功",
    };
  } catch (error) {
    throw { code: 500, msg: "删除省份失败" };
  }
}
/**
 * 删除区县
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function removeDistricts(parmas = {}) {
  try {
    let { id } = parmas;

    let query = `
            REMOVE ${districtsSqlName} WHERE id = ${id}
        `;
    let result = await MYSQL.queryPromise(query);
    return {
      code: 0,
      msg: "删除成功",
    };
  } catch (error) {
    throw { code: 500, msg: "删除省份失败" };
  }
}
/**
 * 新增省份
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function addProvincesList(params = {}) {
  try {
  } catch (error) {
    throw { code: 500, msg: "新增省份失败" };
  }
}
/**
 * 新增城市
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function addCities(params = {}) {
  try {
  } catch (error) {
    throw { code: 500, msg: "新增城市失败" };
  }
}
/**
 * 新增区县
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function addDistricts(params = {}) {
  try {
  } catch (error) {
    throw { code: 500, msg: "新增区县失败" };
  }
}
/**
 * 更新/修改省份信息
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function updateProvinces(params = {}) {
  let { name, type, code, state = 1 } = params;
  if (!name) {
    throw { code: 500, msg: "name不能为空" };
  } else if (!code) {
    throw { code: 500, msg: "code不能为空" };
  } else if (!type) {
    throw { code: 500, msg: "type不能为空" };
  }
}
/**
 * 更新/修改城市信息
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function updateCities(params = {}) {
  let { name, code, state = 1, parent } = params;
  if (!name) {
    throw { code: 500, msg: "name不能为空" };
  } else if (!code) {
    throw { code: 500, msg: "code不能为空" };
  } else if (!parent) {
    throw { code: 500, msg: "parent不能为空" };
  }
}
/**
 * 更新/修改区县信息
 * @param {object} params
 * @param {string} params.user 用户id
 */
async function updateDistricts(params = {}) {
  let { name, code, state = 1, parent, area_code } = params;
  if (!name) {
    throw { code: 500, msg: "name不能为空" };
  } else if (!code) {
    throw { code: 500, msg: "code不能为空" };
  } else if (!parent) {
    throw { code: 500, msg: "parent不能为空" };
  } else if (!area_code) {
    throw { code: 500, msg: "area_code不能为空" };
  }
}
module.exports = {
  provincesList,
  citiesList,
  districtsList,
  removeProvinces,
  removeCities,
  removeDistricts,
  addProvincesList,
  addCities,
  addDistricts,
  updateProvinces,
  updateCities,
  updateDistricts,
  deleteCountry,
  countryList,
  addCountry,

  citiesListAll,
  provincesListAll,
  countryListAll,
};
